﻿// <copyright file="AccessExtensions.cs" company="DotNetLib">
//
// .netlib: http://www.codeplex.com/DotNetLib
//
// This code is released under the New BSD License.
// A copy of this license is available at:
// http://www.codeplex.com/DotNetLib/license
//
// </copyright>

namespace DotNetLib.Microsoft.Office
{
  using System.Data;
  using System.Data.OleDb;
  using System.Globalization;

  /// <summary>
  /// Helper methods to expose additional functionality related to Access.
  /// </summary>
  public static class AccessExtensions
  {
    /// <summary>
    /// Creates and populates a <see cref="DataTable"/> with the contents of a table in a Microsoft Access file.
    /// </summary>
    /// <param name="fileName">The Access database to get data from.</param>
    /// <param name="selectSql">The SQL SELECT statement to run against the Access database.</param>
    /// <returns>A populated <see cref="DataTable"/> with the results of the SELECT statement.</returns>
    public static DataTable Import(string fileName, string selectSql)
    {
      DataTable table = new DataTable();
      table.Locale = CultureInfo.CurrentCulture;
      Import(table, fileName, selectSql);
      return table;
    }

    /// <summary>
    /// Executes a SQL statement against an Access database and returns the number of rows affected.
    /// </summary>
    /// <param name="fileName">The Access database to run the statement against.</param>
    /// <param name="sql">The SQL statement to run against the Access database.</param>
    /// <returns>The number of rows affected.</returns>
    public static int ExecuteNonQuery(string fileName, string sql)
    {
      using (OleDbConnection connection = new OleDbConnection(CreateConnectionString(fileName)))
      using (OleDbCommand command = new OleDbCommand(sql, connection))
      {
        connection.Open();
        return command.ExecuteNonQuery();
      }
    }

    /// <summary>
    /// Populates a <see cref="DataTable"/> with the contents of an Access file.
    /// </summary>
    /// <param name="table">The <see cref="DataTable"/> to populate.</param>
    /// <param name="fileName">The Access database to get data from.</param>
    /// <param name="selectSql">The SQL SELECT statement to run against the Access database.</param>
    public static void Import(this DataTable table, string fileName, string selectSql)
    {
      using (OleDbConnection connection = new OleDbConnection(CreateConnectionString(fileName)))
      using (OleDbCommand command = new OleDbCommand(selectSql, connection))
      using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
      {
        adapter.Fill(table);
      }
    }

    private static string CreateConnectionString(string fileName)
    {
      return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "'";
    }
  }
}
